﻿Imports System.Data
Imports System.Configuration  '必须要在管理器中添加引用    
Imports System.Data.OleDb

''' <summary>  
''' OleDbHelper类是专门提供给广大用户用于高性能、可升级和最佳练习的OleDb数据操作  
''' </summary>  
''' <remarks></remarks>  
Public Class OleDbHelper
    '定义变量    
    '获得数据库的连接字符串    
    Public ReadOnly strConnection As String = HAYJS.ConnStringOle
    '设置连接    
    Dim conn As OleDbConnection = New OleDbConnection(strConnection)
    '定义cmd命令    
    Dim cmd As New OleDbCommand

    ''' <summary>    
    ''' 执行增删改三个操作,(有参)返回值为Boolean类型,确认是否执行成功    
    ''' </summary>    
    ''' <param name="cmdText">需要执行语句,一般是OleDb语句,也有存储过程</param>    
    ''' <param name="cmdType">判断OleDb语句的类型,一般都不是存储过程</param>    
    ''' <param name="paras">参数数组,无法确认有多少参数</param>    
    ''' <returns></returns>    
    ''' <remarks></remarks>    
    Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As OleDbParameter()) As Integer
        '将传入的值,分别为cmd的属性赋值    
        cmd.Parameters.AddRange(paras)   '将参数传入    
        cmd.CommandType = cmdType            '设置一个值,解释cmdText    
        cmd.Connection = conn                '设置连接,全局变量    
        cmd.CommandText = cmdText
        '设置查询的语句    
        Using (conn)
            Try
                conn.Open()
            Catch ex As Exception
                Throw New Exception(ex.Message.ToString() + "数据库连接错误，请确认网络正常！")
            End Try
            '打开连接    
            Using (cmd)
                Return cmd.ExecuteNonQuery()     '执行增删改操作
                cmd.Parameters.Clear()           '清除参数 
            End Using
        End Using

    End Function
    ''' <summary>
    ''' 执行增删改三个操作,(无参)    
    ''' </summary>    
    ''' <param name="cmdText">需要执行语句,一般是OleDb语句,也有存储过程</param>    
    ''' <param name="cmdType">判断OleDb语句的类型,一般都不是存储过程</param>    
    ''' <returns>Interger,受影响的行数</returns>    
    ''' <remarks>2013年2月2日8:19:59</remarks>    
    Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
        '为要执行的命令cmd赋值    
        cmd.CommandText = cmdText       '先是查询的OleDb语句    
        cmd.CommandType = cmdType       '设置OleDb语句如何解释    
        cmd.Connection = conn           '设置连接    

        '执行操作  Using (conn)
        'Try
        Using (conn)
            conn.Open()                      '打开连接    
            Using (cmd)
                Return cmd.ExecuteNonQuery()     '执行增删改操作
                cmd.Parameters.Clear()           '清除参数 
            End Using
        End Using
        'Catch ex As Exception
        '    Throw New Exception("数据库连接错误，请确认网络正常！")
        'End Try
    End Function

    ''' <summary>    
    ''' 执行查询的操作,(有参),参数不限    
    ''' </summary>    
    ''' <param name="cmdText">需要执行语句,一般是OleDb语句,也有存储过程</param>    
    ''' <param name="cmdType">判断OleDb语句的类型,一般都不是存储过程</param>    
    ''' <param name="paras">传入的参数</param>    
    ''' <returns></returns>    
    ''' <remarks></remarks>    
    Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As OleDbParameter()) As DataTable

        Dim OleDbAdapter As OleDbDataAdapter
        Dim dt As New DataTable
        Dim ds As New DataSet
        '还是给cmd赋值    
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Connection = conn
        cmd.Parameters.AddRange(paras)  '参数添加    
        OleDbAdapter = New OleDbDataAdapter(cmd)  '实例化adapter
        Using (conn)
            Try
                conn.Open()
            Catch ex As Exception
                MsgBox(ex.Message.ToString())
                'Throw New Exception("数据库连接错误，请确认网络正常！")
            End Try '打开连接    
            Using (cmd)
                OleDbAdapter.Fill(ds)           '用adapter将dataSet填充     
                dt = ds.Tables(0)             'datatable为dataSet的第一个表    
                cmd.Parameters.Clear()
                Return dt
            End Using
        End Using

    End Function


    ''' <summary>    
    ''' 执行查询的操作,(无参)    
    ''' </summary>    
    ''' <param name="cmdText">需要执行语句,一般是OleDb语句,也有存储过程</param>    
    ''' <param name="cmdType">判断OleDb语句的类型,一般都不是存储过程</param>    
    ''' <returns>dataTable,查询到的表格</returns>    
    ''' <remarks></remarks>    
    Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
        Dim OleDbAdapter As OleDbDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable
        '还是给cmd赋值    
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Connection = conn
        OleDbAdapter = New OleDbDataAdapter(cmd)  '实例化adapter   
        'Try
        Using (conn)
            conn.Open()                      '打开连接    
            Using (cmd)
                OleDbAdapter.Fill(ds)           '用adapter将dataSet填充     
                dt = ds.Tables(0)             'datatable为dataSet的第一个表    
                cmd.Parameters.Clear()
                Return dt
            End Using
        End Using
        'Catch ex As Exception
        '    Throw New Exception("数据库连接错误，请确认网络正常！")
        'End Try
    End Function
End Class